from flask import Flask, request, render_template, jsonify
import pymysql
import logging
import pandas as pd
conn = pymysql.connect(host='127.0.0.1', port=3307, user="root",
                       password="", charset="utf8", db='student')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

app = Flask(__name__)

# 项目日志
log_file = r"C:\Users\17754\Desktop\基于flask框架实现学生管理系统\student_Management.log"
logging.basicConfig(filename=log_file, level=logging.DEBUG)


# 初始展示界面
@app.route("/info/index", methods=["POST", "GET"])
def index():
    if request.method == 'GET':
        return render_template("index.html")
    return render_template("login.html")


# 登录逻辑
@app.route("/info/login", methods=["POST", "GET"])
def login():
    if request.method == "POST":
        uname = request.form.get("uname")
        pws = request.form.get("psw")
        sql = 'select * from login where username = %s'
        cursor.execute(sql, uname)
        dict = cursor.fetchall()
        for item in dict:
            loginname = item['username']
            loginpwd = item['password']
            name = item['name']
            access = item['access']
            if uname == loginname and pws == loginpwd:
                if access == 'A':
                    return render_template('main.html', loginname=name)
                return render_template('studentmain.html',loginname=name)
        return render_template('login.html')
    else:
        return render_template('login.html')


# 新用户注册页面
@app.route('/adduser',methods=['GET'])
def adduser():
    return render_template('adduser.html')


# 注册新用户进入数据库，普通用户
@app.route('/insert/user',methods=['POST'])
def insertuser():
    try:
        if request.method == "POST":
            username = request.form.get('username')
            password = request.form.get('password')
            name = request.form.get('name')
            mobile = request.form.get('mobile')
            address = request.form.get('address')
            sql = 'insert into login values(%s,%s,%s,%s,%s,%s)'
            cursor.execute(sql,(username,password,name,mobile,address,'B'))
            conn.commit()
            return render_template('AddUserSuccess.html')
    except:
       # return render_template('erroruser.html')
        return render_template('AddUserError.html')


# 退出登录逻辑
@app.route("/info/login",methods=["POST"])
def outlogin():
    return render_template('login.html')


# 管理端主面板
@app.route('/info/<name>')
def returnMain(name):
    loginname = name
    # print(loginname)
    return render_template('main.html', loginname=loginname)


# 进入管理端学籍管理界面
@app.route("/info/studentmangement", methods=["GET", "POST"])
#  发送get请求访问管理学籍模块
def stumament():
    sql = 'select * from studentmanagement'
    cursor.execute(sql)
    data = cursor.fetchall()
    return render_template('studentManagement.html', data=data)


# 添加学籍信息
@app.route("/info/addinfo", methods=["POST", "GET"])
def addinfo():
    if request.method == "POST":
        GId = request.form.get("GId")
        sId = request.form.get("sId")
        sName = request.form.get("sName")
        sGender = request.form.get("sGender")
        sDate = request.form.get("sDate")
        sNation = request.form.get("sNation")
        sSchool = request.form.get("sSchool")
        sSpeciality = request.form.get("sSpeciality")
        sClass = request.form.get("sClass")
        sDormitory = request.form.get("sDormitory")
        sMobile = request.form.get("sMobile")
        sAddress = request.form.get("sAddress")
        sql = 'insert into studentmanagement values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)'
        cursor.execute(sql, (GId, sId, sName, sGender, sDate, sNation,
                             sSchool, sSpeciality, sClass,
                             sDormitory, sMobile, sAddress))
        conn.commit()
        sql = 'select * from studentmanagement'
        cursor.execute(sql)
        data = cursor.fetchall()
        return render_template('studentManagement.html', data=data)



# 管理端查询学籍
@app.route('/search', methods=['POST'])
def infosearch():
    search_data = request.get_json().get('data')
    sql = 'select * from studentmanagement where 学籍号 = %s'
    cursor.execute(sql, search_data)
    data = cursor.fetchall()
    for item in data:
        result0 = "查询到的信息：" + str(item)
    return jsonify({"message": result0})


# 管理端修改学籍
@app.route('/update', methods=['POST'])
def save_modified_data():
    modified_data = request.get_json().get('data')
    gid = modified_data[0]
    sId = modified_data[1]
    sName = modified_data[2]
    sGender = modified_data[3]
    sDate = modified_data[4]
    sNation = modified_data[5]
    sSchool = modified_data[6]
    sSpeciality = modified_data[7]
    sClass = modified_data[8]
    sDormitory = modified_data[9]
    sMobile = modified_data[10]
    sAddress = modified_data[11]
    sql = ('update studentmanagement set 学号 = %s,姓名 = %s,'
           '性别 = %s,出生日期 = %s,'
           '民族 = %s,学院 = %s,'
           '专业 = %s,班级 = %s,'
           '宿舍号 = %s,联系方式 = %s,'
           '家庭地址 = %s where 学籍号 = %s')
    cursor.execute(sql, (sId, sName, sGender, sDate, sNation, sSchool, sSpeciality,
                         sClass, sDormitory, sMobile, sAddress, gid))
    conn.commit()
    data = cursor.fetchall()
    return render_template('studentManagement.html', data=data)


# 管理端删除学籍
@app.route('/delete', methods=['POST'])
def delete():
    Gid = request.form.get('id')
    # print(Gid)
    sql = 'delete from studentmanagement where 学籍号 = %s'
    cursor.execute(sql, Gid)
    conn.commit()
    data = cursor.fetchall()
    return render_template('studentManagement.html', data=data)


@app.route('/saveStudent')
def saveStudent():
    try:
        sql = 'select * from studentmanagement '
        cursor.execute(sql)
        conn.commit()
        data = cursor.fetchall()
        dict = data
        df = pd.DataFrame(data)
        # print(data)
        df.to_csv('tempsM.csv', index=False)
        data = pd.read_csv('tempsM.csv')
        data.to_excel('学生学籍表.xlsx', index=False)
        return render_template('studentManagement.html',data=dict)
    except Exception:
        print('ERROR')


# 管理端展示成绩管理面板
@app.route("/info/studentScore", methods=["GET", "POST"])
#  发送get请求访问成绩管理模块
def studentScore():
    sql = 'select * from score'
    cursor.execute(sql)
    data = cursor.fetchall()
    # print(data)
    return render_template('scoreManagement.html', data=data)


# 管理端添加成绩
@app.route('/info/addscore', methods=["POST"])
def addscore():
    try:
        if request.method == "POST":
            id = request.form.get("sid")
            sname = request.form.get("sname")
            datastructure = request.form.get("datastructure")
            application = request.form.get("application")
            network = request.form.get("network")
            chip = request.form.get("chip")
            java = request.form.get("java")
            python = request.form.get("python")
            sql = ('insert into score (学号,姓名,数据结构,软件工程,计算机网络,单片机原理,'
                   'Java程序设计,Python程序设计) values(%s,%s,%s,%s,%s,%s,%s,%s)')
            cursor.execute(sql, (id, sname, datastructure, application, network, chip,
                                 java, python))
            conn.commit()
            sql = 'update score set 总分 = 数据结构 + 软件工程 + 计算机网络 + 单片机原理 + Java程序设计 + Python程序设计'
            cursor.execute(sql)
            conn.commit()
            sql = 'select * from score'
            cursor.execute(sql)
            data = cursor.fetchall()
            # print(data)
            return render_template('scoreManagement.html', data=data)
    except  Exception:
        return '<script>alert("添加失败,学号重复")</script>'


# 管理端删除成绩
@app.route('/deleteScore', methods=["POST"])
def deleteScore():
    id = request.form.get('id')
    # print(id)
    sql = 'delete from score where 学号 = %s'
    cursor.execute(sql, id)
    conn.commit()
    data = cursor.fetchall()
    return render_template('scoreManagement.html', data=data)


# 管理端更新成绩
@app.route('/updateScore', methods=["POST"])
def updateScore():
    update_data = request.get_json().get('data')
    # print(update_data)
    sid = update_data[0]
    name = update_data[1]
    data = update_data[2]
    application = update_data[3]
    network = update_data[4]
    chip = update_data[5]
    java = update_data[6]
    python = update_data[7]
    sql = ('update score set 姓名 = %s,'
           '数据结构 = %s,软件工程 = %s,'
           '计算机网络 = %s,单片机原理 = %s,'
           'Java程序设计 = %s,Python程序设计 = %s where 学号 = %s')
    cursor.execute(sql, (name, data, application, network, chip, java, python, sid))
    conn.commit()
    sql = 'update score set 总分 = 数据结构 + 软件工程 + 计算机网络 + 单片机原理 + Java程序设计 + Python程序设计'
    cursor.execute(sql)
    conn.commit()
    data = cursor.fetchall()
    return render_template('studentManagement.html', data=data)


# 管理端成绩升序
@app.route('/orderASC', methods=['POST'])
def orderASC():
    try:
        # flag = request.get_json().get('data')
        # print(flag)
        sql = 'select * from score order by 总分 '
        cursor.execute(sql)
        conn.commit()
        data = cursor.fetchall()
        df = pd.DataFrame(data)
        # print(data)
        df.to_csv('tempASC.csv', index=False)
        data = pd.read_csv('tempASC.csv')
        data.to_excel('成绩升序表.xlsx', index=False)
        return 'Yes'
    except Exception:
        print('ERROR')


# 管理端成绩降序
@app.route('/orderDESC', methods=['POST'])
def orderDESC():
    try:
        # flag = request.get_json().get('data')
        # print(flag)
        sql = 'select * from score order by 总分 DESC '
        cursor.execute(sql)
        conn.commit()
        data = cursor.fetchall()
        # print(data)
        df = pd.DataFrame(data)
        # print(df)
        df.to_csv('tempDESC.csv', index=False)
        data = pd.read_csv('tempDESC.csv')
        data.to_excel('成绩降序表.xlsx', index=False)
        #df.to_excel('output.xlsx', index=False, engine='openpyxl')
        #data_str = str(df)
        #with open('data.csv', 'w', encoding='utf-8') as f:
            #f.write(data_str)
        #df.to_excel('output.xlsx', index=False, engine='openpyxl')
        '''for item in data:
            data_str = str(item)
            with open('data.txt', 'w', encoding='utf-8') as f:
                f.write(data_str)'''
        #return render_template('scoreManagement.html', data=data)
        return 'Yes'
    except Exception:
        print('Error')


# 管理端搜索成绩
@app.route('/searchScore', methods=['POST'])
def searchScore():
    data = request.get_json()  # 获取POST请求的JSON数据
    search_term = data.get('search_data')  # JSON数据包含搜索词
    #print(search_term)
    sql = 'select * from score where 学号 = %s'
    cursor.execute(sql, search_term)
    data = cursor.fetchall()
    #print(data)
    for item in data:
       dataitem = "查询到的信息：" + str(item)
       #print(data1)
    return jsonify(dataitem)


#@app.route('/infomationView',methods=['POST'])
#def infomationView():
#    return '6666666'

# 管理端查看个人信息
@app.route('/information/<name>',methods=['POST','GET'])
def information(name):
    sql = 'select * from login where name = %s'
    cursor.execute(sql,name)
    data = cursor.fetchall()
    # print(data[0])
    value = data[0]['name']          # 从MySQL返回一个列表字典，取出字典   再取出需要的值
    #print(value)
    return render_template('informationView.html',dict = data[0],name=value)


# 管理用户
@app.route('/usermanagement',methods=['GET','POST'])
def usermanagement():
    # 获取login表中的数据并传入前端
    sql = 'select * from login'
    cursor.execute(sql)
    dict = cursor.fetchall()
    # print(dict)
    # print(dict[0])
    # for item in dict:
    #     data = item
    # print(data)
    return render_template('userManagement.html',dict=dict)



# 用户修改逻辑  规定：用户名不可更改
@app.route('/updateuser',methods=['GET','POST'])
def updatauser():
    # 获取前端修改后传入的数据
    modified_data = request.get_json().get('data')
    #print(modified_data)

    # 从列表中获取数据
    username = modified_data[0]
    password = modified_data[1]
    name = modified_data[2]
    mobile = modified_data[3]
    address = modified_data[4]
    access = modified_data[5]

    # 更新数据库
    sql = 'update login set password = %s,name = %s,mobile = %s,address =%s,access=%s where username=%s'
    cursor.execute(sql,(password,name,mobile,address,access,username))
    conn.commit()
    data = cursor.fetchall()
    return render_template('userManagement.html', dict=data)


# 用户删除逻辑
@app.route('/deleteuser',methods=['GET','POST'])
def deleteuser():
    # 获取前端传入的对应的关键值    在这里为用户名
    username = request.form.get('id')
    # print(username)
    # 更新数据库
    sql = 'delete from login where username=%s'
    cursor.execute(sql,username)
    conn.commit()
    data = cursor.fetchall()
    #return render_template('scoreManagement.html', data=data)
    return render_template('scoreManagement.html', dict=data)


# 学生端主页视图
@app.route('/infos/<name>')
def studentmain(name):
    name = name
    return render_template('studentmain.html', loginname=name)


# 学生端查看个人信息视图
@app.route('/informations/<name>',methods=['POST','GET'])
def informations(name):
    sql = 'select * from login where name = %s'
    cursor.execute(sql,name)
    data = cursor.fetchall()
    # print(data[0])
    # 从MySQL返回一个列表字典，取出字典   再取出需要的值
    value = data[0]['name']
    # print(value)
    return render_template('studentView.html',dict = data[0],name=value)


# 学生端查看自己成绩
@app.route('/scoreView/<name>',methods=['GET','POST'])
def socreView(name):
    # print(name)
    sql = 'select * from score where 姓名 = %s'
    cursor.execute(sql,name)
    data = cursor.fetchall()
    # print(data)
    # print(data[0])
    # data[0]  将列表中的字典传入jinja2模版
    return render_template('scoreView.html',name=name,data = data[0])



if __name__ == '__main__':
    app.run()
    # 本地项目日志
    logging.debug(app.run())
    # 释放数据库的连接
    conn.close()
    cursor.close()
